/**
 * Licensed to Jasig under one or more contributor license
 * agreements. See the NOTICE file distributed with this work
 * for additional information regarding copyright ownership.
 * Jasig licenses this file to you under the Apache License,
 * Version 2.0 (the "License"); you may not use this file
 * except in compliance with the License. You may obtain a
 * copy of the License at:
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on
 * an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */

package org.jasig.portal.rdbm;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;


/**
 * @author Eric Dalquist <a href="mailto:edalquist@unicon.net">edalquist@unicon.net</a>
 * @version $Revision$ $Date$
 */
public class DatabaseMetaDataImpl implements IDatabaseMetadata, InitializingBean {
    public static final class PostgreSQLDb extends JoinQueryString {
        public PostgreSQLDb(final String testString) {
            super(testString);
        }
    }

    public static final class OracleDb extends JoinQueryString {
        public OracleDb(final String testString) {
            super(testString);
        }
    }

    public static final class JdbcDb extends JoinQueryString {
        public JdbcDb(final String testString) {
          super(testString);
        }
    }

    private static final Log LOG = LogFactory.getLog(DatabaseMetaDataImpl.class);

    /** Define the oracle TO_DATE format */

    //Define the different join queries we know about with the
    //appropriately typed JoinQueryString implementation.
    private static final JoinQueryString jdbcDb = new DatabaseMetaDataImpl.JdbcDb("{oj UP_USER LEFT OUTER JOIN UP_USER_LAYOUT ON UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID} WHERE");
    private static final JoinQueryString postgreSQLDb = new DatabaseMetaDataImpl.PostgreSQLDb("UP_USER LEFT OUTER JOIN UP_USER_LAYOUT ON UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID WHERE");
    private static final JoinQueryString oracleDb = new DatabaseMetaDataImpl.OracleDb("UP_USER, UP_USER_LAYOUT WHERE UP_USER.USER_ID = UP_USER_LAYOUT.USER_ID(+) AND");

    /** Array of join tests to perform. */
    private static final JoinQueryString[] joinTests = {oracleDb, postgreSQLDb, jdbcDb};

    /** The {@link DataSource} that represents the server */
    private final DataSource dataSource;
    private final PlatformTransactionManager transactionManager;
    private TransactionTemplate transactionTemplate;

    /** The {@link IJoinQueryString} to use for performing outer joins */
    private IJoinQueryString joinTest = null;
    private boolean dbmdSupportsOuterJoins = false;

    //Database meta information
    private boolean portalTablesExist = false;
    private boolean useTSWrapper = false;
    private boolean useToDate = false;
    private String databaseProductName = null;
    private String databaseProductVersion = null;
    private String driverName = null;
    private String driverVersion = null;
    private String userName = null;
    private String dbUrl = null;


    /**
     * Creates a new {@link DatabaseMetaDataImpl} with the specified
     * {@link DataSource}.
     *
     * @param ds The {@link DataSource} to use as the base for this server interface.
     */
    public DatabaseMetaDataImpl(final DataSource ds, final PlatformTransactionManager transactionManager) {
        if (ds == null)
            throw new IllegalArgumentException("DataSource cannot be null");

        this.dataSource = ds;
        this.transactionManager = transactionManager;
    }

    /* (non-Javadoc)
     * @see org.springframework.beans.factory.InitializingBean#afterPropertiesSet()
     */
    public void afterPropertiesSet() throws Exception {
    	this.transactionTemplate = new TransactionTemplate(this.transactionManager);
    	this.transactionTemplate.setPropagationBehavior(TransactionTemplate.PROPAGATION_REQUIRES_NEW);
    	this.transactionTemplate.setReadOnly(true);
    	this.transactionTemplate.afterPropertiesSet();    	
    	
    	this.runDatabaseTests();
        if (LOG.isDebugEnabled())
            LOG.debug(this.toString());            
    }

    private void releaseConnection(final Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        }
        catch (Exception e) {
            LOG.warn("An error occured while closing a connection.", e);
        }
    }

    /**
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#getJoinQuery()
     */
    public final IJoinQueryString getJoinQuery() {
        return this.joinTest;
    }

    /**
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#supportsOuterJoins()
     */
    public final boolean supportsOuterJoins() {
        return (this.joinTest != null);
    }

    /**
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#supportsTransactions()
     */
    public final boolean supportsTransactions() {
    	//We never run on DBs that don't support transactions any more
        return true;
    }

    /**
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#supportsPreparedStatements()
     */
    public final boolean supportsPreparedStatements() {
        //We never run on DBs that don't support prepared statements any more
        return true;
    }
    
    /* (non-Javadoc)
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#getJdbcDriver()
     */
    public String getJdbcDriver() {
        return this.driverName;
    }
    
    /* (non-Javadoc)
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#getDatabaseProductName()
     */
    public String getDatabaseProductName() {
        return this.databaseProductName;
    }

    /* (non-Javadoc)
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#getDatabaseProductVersion()
     */
    public String getDatabaseProductVersion() {
        return this.databaseProductVersion;
    }

    /* (non-Javadoc)
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#getJdbcDriverVersion()
     */
    public String getJdbcDriverVersion() {
        return this.driverVersion;
    }

    /* (non-Javadoc)
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#getJdbcUrl()
     */
    public String getJdbcUrl() {
        return this.dbUrl;
    }

    /* (non-Javadoc)
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#getJdbcUser()
     */
    public String getJdbcUser() {
        return this.userName;
    }

    /**
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#sqlTimeStamp()
     */
    public String sqlTimeStamp() {
        return this.sqlTimeStamp(System.currentTimeMillis());
    }

    /**
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#sqlTimeStamp(long)
     */
    public String sqlTimeStamp(final long date) {
        final StringBuffer sqlTS = new StringBuffer();

        if (useToDate) {
            final SimpleDateFormat oracleToDate = new SimpleDateFormat("yyyy MM dd HH:mm:ss");
            sqlTS.append("TO_DATE('");
            sqlTS.append(oracleToDate.format(new Date(date)));
            sqlTS.append("', 'YYYY MM DD HH24:MI:SS')");
        }
        else if (useTSWrapper) {
            sqlTS.append("{ts '");
            sqlTS.append(new Timestamp(date).toString());
            sqlTS.append("'}");
        }
        else {
            sqlTS.append("'");
            sqlTS.append(new Timestamp(date).toString());
            sqlTS.append("'");
        }

        return sqlTS.toString();
    }

    /**
     * @see org.jasig.portal.rdbm.IDatabaseMetadata#sqlTimeStamp(java.util.Date)
     */
    public String sqlTimeStamp(final Date date) {
        if (date == null) {
            return "NULL";
        }
         
        return this.sqlTimeStamp(date.getTime());
    }


    @Override
    public String toString() {
        final StringBuilder dbInfo = new StringBuilder();

        dbInfo.append(this.databaseProductName);
        dbInfo.append(" (");
        dbInfo.append(this.databaseProductVersion);
        dbInfo.append(") / ");
        dbInfo.append(this.driverName);
        dbInfo.append(" (");
        dbInfo.append(this.driverVersion);
        dbInfo.append(") database/driver");
        dbInfo.append("\n");
        dbInfo.append("    Connected To: ");
        dbInfo.append(this.dbUrl);
        dbInfo.append("\n");
        
        if (this.portalTablesExist) {
            dbInfo.append("    Supports:");
            dbInfo.append("\n");
            dbInfo.append("        Outer Joins:          ");
            dbInfo.append(this.supportsOuterJoins());
            dbInfo.append("\n");
            dbInfo.append("        {ts metasyntax:       ");
            dbInfo.append(this.useTSWrapper);
            dbInfo.append("\n");
            dbInfo.append("        TO_DATE():            ");
            dbInfo.append(this.useToDate);
        }
        else {
            dbInfo.append("    WARNING: uPortal tables do no exist, not all meta-data tests were executed.");
        }

        return dbInfo.toString();
    }




    /**
     * Run a set of tests on the database to provide better meta data.
     */
    private void runDatabaseTests() {
    	Connection conn = null;
    	try {
    		conn = this.dataSource.getConnection();
    		//The order of these tests is IMPORTANT, each may depend on the
    		//results of the previous tests.
    		this.getMetaData(conn);
    		final SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(this.dataSource);
    		
    		this.testDatabaseInitialized(jdbcTemplate);
    		if (this.portalTablesExist) {
        		this.testOuterJoins(jdbcTemplate);
        		this.testTimeStamp(jdbcTemplate);
    		}

    	} catch (SQLException e) {
    		LOG.error("Error during database initialization. ", e);
    		/*
    		 * We must throw a RuntimeException here to avoid starting the portal
    		 * with incorrect assumptions about what the database supports.
    		 */
    		throw new DataAccessResourceFailureException("Error during database initialization. ",e);
    	}finally{
    		this.releaseConnection(conn);
    	}
    }

    /**
     * Gets meta data about the connection.
     */
    private void getMetaData(final Connection conn) {
        try {
            final DatabaseMetaData dmd = conn.getMetaData();

            this.databaseProductName = dmd.getDatabaseProductName();
            this.databaseProductVersion = dmd.getDatabaseProductVersion();
            this.driverName = dmd.getDriverName();
            this.driverVersion = dmd.getDriverVersion();
            this.userName = dmd.getUserName();
            this.dbUrl = dmd.getURL();
            this.dbmdSupportsOuterJoins = dmd.supportsOuterJoins();
        }
        catch (SQLException sqle) {
            LOG.error("Error getting database meta data.", sqle);
        }
    }

    /**
     * Tests if the uPortal tables exist that are needed for this test. 
     */
    private void testDatabaseInitialized(final SimpleJdbcTemplate jdbcTemplate) {
        try {
            jdbcTemplate.queryForInt("SELECT COUNT(USER_ID) FROM UP_USER");
            this.portalTablesExist = true;
        }
        catch (BadSqlGrammarException bsge) {
            LOG.warn("The uPortal database is not initialized, the database tests will not be performed.");
        }
    }

    /**
     * Test the database to see if it really supports outer joins.
     * @param conn The connection to use.
     */
    private void testOuterJoins(final SimpleJdbcTemplate jdbcTemplate) {
        if (this.dbmdSupportsOuterJoins) {
            for (final JoinQueryString joinQueryString : joinTests) {
                final String joinTestQuery =
                    "SELECT COUNT(UP_USER.USER_ID) " +
                    "FROM " + joinQueryString.getTestJoin() + " UP_USER.USER_ID=0";
                
                try {
                	transactionTemplate
							.execute(new TransactionCallbackWithoutResult() {
								@Override
                                public void doInTransactionWithoutResult(
										TransactionStatus status) {
									jdbcTemplate.getJdbcOperations().execute(
											joinTestQuery);
								}
							});
                    
                    this.joinTest = joinQueryString;
                    if (LOG.isDebugEnabled()) {
                        LOG.debug("Using join test: " + this.joinTest.getClass().getName());
                    }
                    break;
                }
                catch (Exception e) {
                    final String logMessage = "Join test failed: " +joinQueryString.getClass().getName() + " on statement: '" + joinTestQuery + "':";
                    
                    if (LOG.isDebugEnabled()) {
                        LOG.debug(logMessage, e);
                    }
                }
            }
        }
    }

    /**
     * Test the database to find the supported timestamp format
     */
    private void testTimeStamp(final SimpleJdbcTemplate jdbcTemplate) {
        try {
            //Try using {ts }
            final String timeStampTestQuery =
                "SELECT USER_ID " +
                "FROM UP_USER " +
                "WHERE LST_CHAN_UPDT_DT={ts '2001-01-01 00:00:00.0'} AND USER_ID = 0";

            jdbcTemplate.queryForList(timeStampTestQuery);
            this.useTSWrapper = true;
        }
        catch (DataAccessException dae1) {
            final String logMessage1 = "Error running {ts } test.";
            if (LOG.isDebugEnabled()) {
                LOG.debug(logMessage1, dae1);
            }

            //Try using TO_DATE()
            try {
                final String toDateTestQuery =
                    "SELECT USER_ID " +
                    "FROM UP_USER " +
                    "WHERE LST_CHAN_UPDT_DT>TO_DATE('2001 01 01 00:00', 'YYYY MM DD HH24:MI:SS') AND USER_ID=0";

                jdbcTemplate.queryForList(toDateTestQuery);
                this.useToDate = true;
            }
            catch (DataAccessException dae2) {
                final String logMessage2 = "Error running TO_DATE() test.";
                if (LOG.isDebugEnabled()) {
                    LOG.debug(logMessage2, dae2);
                }
            }
        }
    }

}
